package com.javaforever.dictionary.daoimpl;

import com.javaforever.dictionary.dao.DicGroupDao;
import com.javaforever.dictionary.domain.DicGroup;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DicGroupDaoImpl implements DicGroupDao{
	public List<DicGroup> listAllDicGroups(Connection connection) throws Exception{
		try {
			String query = "select parent_id,dic_group_key,dic_group_value,description,dic_group_name,active,dic_group_id from dic_dic_groups;";
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet result = ps.executeQuery();
			ArrayList<DicGroup> list = new ArrayList<DicGroup>();
			while(result.next()) {
				//Build the list object.
				DicGroup dicGroup = new DicGroup();
				dicGroup.setParentId(result.getLong("parent_id"));
				dicGroup.setDicGroupKey(result.getString("dic_group_key"));
				dicGroup.setDicGroupValue(result.getString("dic_group_value"));
				dicGroup.setDescription(result.getString("description"));
				dicGroup.setDicGroupName(result.getString("dic_group_name"));
				dicGroup.setActive(result.getBoolean("active"));
				dicGroup.setDicGroupId(result.getLong("dic_group_id"));
				//Build the object list.
				list.add(dicGroup);
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return new ArrayList<DicGroup>();
		}
	}

	public boolean updateDicGroup(Connection connection,DicGroup dicGroup) throws Exception{
		String query = "update dic_dic_groups set parent_id = ? ,dic_group_key = ? ,dic_group_value = ? ,description = ? ,dic_group_name = ? ,active = ? where dic_group_id = ?;";
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1,dicGroup.getParentId());
		ps.setString(2,dicGroup.getDicGroupKey());
		ps.setString(3,dicGroup.getDicGroupValue());
		ps.setString(4,dicGroup.getDescription());
		ps.setString(5,dicGroup.getDicGroupName());
		ps.setBoolean(6,dicGroup.getActive());
		ps.setLong(7,dicGroup.getDicGroupId());
		int result = ps.executeUpdate();
		if (result > 0) {
			return true;
		}
		return false;
	}

	public boolean deleteDicGroup(Connection connection,Long dicGroupId) throws Exception{
		String query = "delete from dic_dic_groups where dic_group_id = ?;";
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1,dicGroupId);
		int result = ps.executeUpdate();
		if (result > 0) {
			return true;
		}
		return false;
	}

	public boolean addDicGroup(Connection connection,DicGroup dicGroup) throws Exception{
		String query = "insert into dic_dic_groups ( parent_id,dic_group_key,dic_group_value,description,dic_group_name,active) values (?,?,?,?,?,?);";
		PreparedStatement ps = connection.prepareStatement(query,PreparedStatement.RETURN_GENERATED_KEYS);
		ps.setLong(1,dicGroup.getParentId());
		ps.setString(2,dicGroup.getDicGroupKey());
		ps.setString(3,dicGroup.getDicGroupValue());
		ps.setString(4,dicGroup.getDescription());
		ps.setString(5,dicGroup.getDicGroupName());
		ps.setBoolean(6,dicGroup.getActive());
		int result = ps.executeUpdate();
		ResultSet rsNewId = ps.getGeneratedKeys();
		if (rsNewId.next()) { 
			long newId = rsNewId.getLong(1);
			dicGroup.setDicGroupId(newId);
		}
		if (result > 0) {
			return true;
		}
		return false;
	}

	public boolean softDeleteDicGroup(Connection connection,Long dicGroupId) throws Exception{
		String query = "update dic_dic_groups set active = false where dic_group_id = ? ;";
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1,dicGroupId);
		int result = ps.executeUpdate();
		if (result > 0) {
			return true;
		}
		return false;
	}

	public DicGroup findDicGroupByDicGroupId(Connection connection,Long dicGroupId) throws Exception{
		try {
			String query = "select parent_id,dic_group_key,dic_group_value,description,dic_group_name,active,dic_group_id from dic_dic_groups where dic_group_id = ?;";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setLong(1,dicGroupId);
			ResultSet result = ps.executeQuery();
			DicGroup dicGroup = new DicGroup();
			result.last();
			//Build the object.
			dicGroup.setParentId(result.getLong("parent_id"));
			dicGroup.setDicGroupKey(result.getString("dic_group_key"));
			dicGroup.setDicGroupValue(result.getString("dic_group_value"));
			dicGroup.setDescription(result.getString("description"));
			dicGroup.setDicGroupName(result.getString("dic_group_name"));
			dicGroup.setActive(result.getBoolean("active"));
			dicGroup.setDicGroupId(result.getLong("dic_group_id"));
			return dicGroup;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public DicGroup findDicGroupByDicGroupName(Connection connection,String dicGroupName) throws Exception{
		try {
			String query = "select parent_id,dic_group_key,dic_group_value,description,dic_group_name,active,dic_group_id from dic_dic_groups where dic_group_name = ?;";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setString(1,dicGroupName);
			ResultSet result = ps.executeQuery();
			DicGroup dicGroup = new DicGroup();
			result.last();
			//Build the object.
			dicGroup.setParentId(result.getLong("parent_id"));
			dicGroup.setDicGroupKey(result.getString("dic_group_key"));
			dicGroup.setDicGroupValue(result.getString("dic_group_value"));
			dicGroup.setDescription(result.getString("description"));
			dicGroup.setDicGroupName(result.getString("dic_group_name"));
			dicGroup.setActive(result.getBoolean("active"));
			dicGroup.setDicGroupId(result.getLong("dic_group_id"));
			return dicGroup;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public List<DicGroup> searchDicGroupsByDicGroupName(Connection connection,String dicGroupName) throws Exception{
		try {
			String query = "select parent_id,dic_group_key,dic_group_value,description,dic_group_name,active,dic_group_id from dic_dic_groups where dic_group_name like ?";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setString(1,"%"+dicGroupName+"%");
			ResultSet result = ps.executeQuery();
			ArrayList<DicGroup> list = new ArrayList<DicGroup>();
			while(result.next()) {
				//Build the list object.
				DicGroup dicGroup = new DicGroup();
				dicGroup.setParentId(result.getLong("parent_id"));
				dicGroup.setDicGroupKey(result.getString("dic_group_key"));
				dicGroup.setDicGroupValue(result.getString("dic_group_value"));
				dicGroup.setDescription(result.getString("description"));
				dicGroup.setDicGroupName(result.getString("dic_group_name"));
				dicGroup.setActive(result.getBoolean("active"));
				dicGroup.setDicGroupId(result.getLong("dic_group_id"));
				//Build the object list.
				list.add(dicGroup);
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public List<DicGroup> listActiveDicGroups(Connection connection) throws Exception{
		try {
			String query = "select parent_id,dic_group_key,dic_group_value,description,dic_group_name,active,dic_group_id from dic_dic_groups where active = true;";
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet result = ps.executeQuery();
			ArrayList<DicGroup> list = new ArrayList<DicGroup>();
			while(result.next()) {
				//Build the list object.
				DicGroup dicGroup = new DicGroup();
				dicGroup.setParentId(result.getLong("parent_id"));
				dicGroup.setDicGroupKey(result.getString("dic_group_key"));
				dicGroup.setDicGroupValue(result.getString("dic_group_value"));
				dicGroup.setDescription(result.getString("description"));
				dicGroup.setDicGroupName(result.getString("dic_group_name"));
				dicGroup.setActive(result.getBoolean("active"));
				dicGroup.setDicGroupId(result.getLong("dic_group_id"));
				//Build the object list.
				list.add(dicGroup);
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public List<DicGroup> listAllDicGroupsByPage(Connection connection,int pagesize,int pagenum) throws Exception{
		try {
			String query = "select parent_id,dic_group_key,dic_group_value,description,dic_group_name,active,dic_group_id from dic_dic_groups limit ?,?;";
			int limitstart = (pagenum-1)*pagesize;
			int limitcount = pagesize;
			PreparedStatement ps = connection.prepareStatement(query);
			int pagecount = this.countDicGroupsPage(connection,pagesize);
			if (pagenum <= 1) pagenum = 1;
			if (pagenum >= pagecount) pagenum = pagecount;
			ps.setInt(1,limitstart);
			ps.setInt(2,limitcount);
			ResultSet result = ps.executeQuery();
			ArrayList<DicGroup> list = new ArrayList<DicGroup>();
			while(result.next()) {
				//Build the list object.
				DicGroup dicGroup = new DicGroup();
				dicGroup.setParentId(result.getLong("parent_id"));
				dicGroup.setDicGroupKey(result.getString("dic_group_key"));
				dicGroup.setDicGroupValue(result.getString("dic_group_value"));
				dicGroup.setDescription(result.getString("description"));
				dicGroup.setDicGroupName(result.getString("dic_group_name"));
				dicGroup.setActive(result.getBoolean("active"));
				dicGroup.setDicGroupId(result.getLong("dic_group_id"));
				//Build the object list.
				list.add(dicGroup);
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return new ArrayList<DicGroup>();
		}
	}

	public void deleteAllDicGroups(Connection connection,String ids) throws Exception{
		String [] idArr = ids.split(",");
		for (String idString : idArr){
			Long dicGroupId = Long.valueOf(idString);
			deleteDicGroup(connection,dicGroupId);
		}
	}

	public void softDeleteAllDicGroups(Connection connection,String ids) throws Exception{
		String [] idArr = ids.split(",");
		for (String idString : idArr){
			Long dicGroupId = Long.valueOf(idString);
			softDeleteDicGroup(connection,dicGroupId);
		}
	}

	public Boolean toggleDicGroup(Connection connection,Long dicGroupId) throws Exception{
		String query = "update dic_dic_groups set active = not active where dic_group_id = ? ";
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1,dicGroupId);
		int result = ps.executeUpdate();
		if (result > 0) {
			return true;
		}
		return false;
	}

	public List<DicGroup> searchDicGroupsByFieldsByPage(Connection connection,DicGroup dicGroup,Long pagenum,Long pagesize) throws Exception{
		try {
		Long start = (pagenum-1)*pagesize;
		Long limit = pagesize;
		String query = " select parent_id,dic_group_key,dic_group_value,description,dic_group_name,active,dic_group_id from dic_dic_groups where 1=1 ";
		if (dicGroup.getParentId()!=null ){
			query += " and parent_id = ? ";
		}
		if (dicGroup.getDicGroupKey()!=null && !"".equals(dicGroup.getDicGroupKey())){
			query += " and dic_group_key like ? ";
		}
		if (dicGroup.getDicGroupValue()!=null && !"".equals(dicGroup.getDicGroupValue())){
			query += " and dic_group_value like ? ";
		}
		if (dicGroup.getDescription()!=null && !"".equals(dicGroup.getDescription())){
			query += " and description like ? ";
		}
		if (dicGroup.getDicGroupName()!=null && !"".equals(dicGroup.getDicGroupName())){
			query += " and dic_group_name like ? ";
		}
		if (dicGroup.getActive()!=null ){
			query += " and active = ? ";
		}
		query += "limit ? offset ?;";
		//System.out.println("JerryDebug:query:"+query);
		
		Integer serial = 1;
		PreparedStatement ps = connection.prepareStatement(query);
		
		if (dicGroup.getParentId()!=null ){
			ps.setLong(serial++,dicGroup.getParentId());
		}
		if (dicGroup.getDicGroupKey()!=null && !"".equals(dicGroup.getDicGroupKey())){
			ps.setString(serial++,"%"+dicGroup.getDicGroupKey()+"%");
		}
		if (dicGroup.getDicGroupValue()!=null && !"".equals(dicGroup.getDicGroupValue())){
			ps.setString(serial++,"%"+dicGroup.getDicGroupValue()+"%");
		}
		if (dicGroup.getDescription()!=null && !"".equals(dicGroup.getDescription())){
			ps.setString(serial++,"%"+dicGroup.getDescription()+"%");
		}
		if (dicGroup.getDicGroupName()!=null && !"".equals(dicGroup.getDicGroupName())){
			ps.setString(serial++,"%"+dicGroup.getDicGroupName()+"%");
		}
		if (dicGroup.getActive()!=null ){
			ps.setBoolean(serial++,dicGroup.getActive());
		}
		ps.setLong(serial++, limit);
		ps.setLong(serial++,start);
		
		ResultSet result = ps.executeQuery();
		ArrayList<DicGroup> list = new ArrayList<DicGroup>();
		while(result.next()) {
			//Build the list object.
			DicGroup dicGroup2 = new DicGroup();
			dicGroup2.setParentId(result.getLong("parent_id"));
			dicGroup2.setDicGroupKey(result.getString("dic_group_key"));
			dicGroup2.setDicGroupValue(result.getString("dic_group_value"));
			dicGroup2.setDescription(result.getString("description"));
			dicGroup2.setDicGroupName(result.getString("dic_group_name"));
			dicGroup2.setActive(result.getBoolean("active"));
			dicGroup2.setDicGroupId(result.getLong("dic_group_id"));
			//Build the object list.
			list.add(dicGroup2);
		}
		return list;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public int countDicGroupsPage(Connection connection,int pagesize) throws Exception{
		try {
			int pagecount = 1;
			double recordcount = 0;
			String query = "select count(dic_group_id) as countNum from dic_dic_groups;";
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet result = ps.executeQuery();
			while(result.next()) {
				recordcount = result.getDouble("countNum");
				pagecount = (int)Math.ceil(recordcount/pagesize);
				if (pagecount <= 1)pagecount = 1;
			}
			return pagecount;
		} catch (Exception e){
			e.printStackTrace();
			return 1;
		}
	}

	public Long countActiveDicGroupRecords(Connection connection) throws Exception{
		String query = "select count(dic_group_id) as countNum from dic_dic_groups where active = true;";
		PreparedStatement ps = connection.prepareStatement(query);
		ResultSet result = ps.executeQuery();
		Long recordcount = 0L;
		while(result.next()) {
			recordcount = result.getLong("countNum");
		}
		return recordcount;
	}

	public Long countAllDicGroupRecords(Connection connection) throws Exception{
		String query = "select count(dic_group_id) as countNum from dic_dic_groups;";
		PreparedStatement ps = connection.prepareStatement(query);
		ResultSet result = ps.executeQuery();
		Long recordcount = 0L;
		while(result.next()) {
			recordcount = result.getLong("countNum");
		}
		return recordcount;
	}

	public Long countSearchDicGroupsByFieldsRecords(Connection connection,DicGroup dicGroup){
		try {
		String query = " select count(*) countSum from dic_dic_groups where 1=1 ";
		if (dicGroup.getParentId()!=null ){
			query += " and parent_id = ? ";
		}
		if (dicGroup.getDicGroupKey()!=null && !"".equals(dicGroup.getDicGroupKey())){
			query += " and dic_group_key like ? ";
		}
		if (dicGroup.getDicGroupValue()!=null && !"".equals(dicGroup.getDicGroupValue())){
			query += " and dic_group_value like ? ";
		}
		if (dicGroup.getDescription()!=null && !"".equals(dicGroup.getDescription())){
			query += " and description like ? ";
		}
		if (dicGroup.getDicGroupName()!=null && !"".equals(dicGroup.getDicGroupName())){
			query += " and dic_group_name like ? ";
		}
		if (dicGroup.getActive()!=null ){
			query += " and active = ? ";
		}
		query += ";";
		//System.out.println("JerryDebug:query:"+query);
		
		int serial = 1;
		PreparedStatement ps = connection.prepareStatement(query);
		
		if (dicGroup.getParentId()!=null ){
			ps.setLong(serial++,dicGroup.getParentId());
		}
		if (dicGroup.getDicGroupKey()!=null && !"".equals(dicGroup.getDicGroupKey())){
			ps.setString(serial++,"%"+dicGroup.getDicGroupKey()+"%");
		}
		if (dicGroup.getDicGroupValue()!=null && !"".equals(dicGroup.getDicGroupValue())){
			ps.setString(serial++,"%"+dicGroup.getDicGroupValue()+"%");
		}
		if (dicGroup.getDescription()!=null && !"".equals(dicGroup.getDescription())){
			ps.setString(serial++,"%"+dicGroup.getDescription()+"%");
		}
		if (dicGroup.getDicGroupName()!=null && !"".equals(dicGroup.getDicGroupName())){
			ps.setString(serial++,"%"+dicGroup.getDicGroupName()+"%");
		}
		if (dicGroup.getActive()!=null ){
			ps.setBoolean(serial++,dicGroup.getActive());
		}
		
		ResultSet result = ps.executeQuery();
		Long countSum = 0L;
		while(result.next()) {
			countSum = result.getLong("countSum");
		}
		return countSum;
		} catch (Exception e){
			e.printStackTrace();
			return 0L;
		}
	}

}
